package persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import bean.AlumnoVO;
import bean.CursoVO;
import bean.MateriaVO;
import bean.ProfesorVO;


public class CursoDAO {

private static CursoDAO ins;
	
	private CursoDAO(){
		
	}
	
	public static CursoDAO getInstancia(){
		
		if(ins == null)
			ins = new CursoDAO();
		return ins;
		
	}
	
	public CursoVO obtener(CursoVO arg){
		
		CursoVO c = null;
		String sql = "Select * from Curso Where numero = ?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, arg.getNumeroCurso());
			rs = stmt.executeQuery();
			if(rs.next()){
				c = new CursoVO();
				c.setNumeroCurso(rs.getInt(1));
				MateriaVO mVO = new MateriaVO(); 
				mVO.setNumero(rs.getString(2));
				c.setMateriaVO(MateriaDAO.getInstancia().obtener(mVO));
				ProfesorVO pVO = new ProfesorVO();
				pVO.setNroLegajo(rs.getInt(3));
				c.setProfesorVO(ProfesorDAO.getInstancia().obtener(pVO));
				c.setCantidadMaxima(rs.getInt(4));
				c.setAlumnosVO(obtenerAlumnos(c));
				
			}
			DataSource.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return c;
		
	}
	
	
	public Vector<CursoVO> obtenerTodos(){
		
        Vector<CursoVO> cursos = new Vector<CursoVO>();
		String sql = "Select * from Curso";
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				CursoVO c= new CursoVO();
				c.setNumeroCurso(rs.getInt(1));
				MateriaVO mVO = new MateriaVO(); 
				mVO.setNumero(rs.getString(2));
				c.setMateriaVO(MateriaDAO.getInstancia().obtener(mVO));
				ProfesorVO pVO = new ProfesorVO();
				pVO.setNroLegajo(rs.getInt(3));
				c.setProfesorVO(ProfesorDAO.getInstancia().obtener(pVO));
				c.setCantidadMaxima(rs.getInt(4));
				c.setAlumnosVO(obtenerAlumnos(c));
				cursos.add(c);
			}
			DataSource.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cursos;
		
	}
	
	
	public void guardar(CursoVO c){
		
		String sql = "Insert into Curso values (?,?,?); Select @@Identity";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, c.getMateriaVO().getNumero());
			stmt.setInt(2, c.getProfesorVO().getNroLegajo());
			stmt.setInt(3, c.getCantidadMaxima());
			rs = stmt.executeQuery();
			if(rs.next()){
				c.setNumeroCurso(rs.getInt(1));
				guardarAlumnos(c.getAlumnosVO(),c);
			}
			
			DataSource.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
	
	
	public void actualizar(CursoVO c){
		
		String sql = "Update Curso Set nroMateria = ?, legProfesor = ?, capacidad = ? Where numero =? ";
		PreparedStatement stmt = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, c.getMateriaVO().getNumero());
			stmt.setInt(2, c.getProfesorVO().getNroLegajo());
			stmt.setInt(3, c.getCantidadMaxima());
			stmt.setInt(4, c.getNumeroCurso());
			stmt.execute();
			DataSource.close();
			actualizarAlumnos(c.getAlumnosVO(),c);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
	
	public void eliminar(CursoVO c){
		
		String sql = "Delete Curso Where numero = ? ";
		PreparedStatement stmt = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, c.getNumeroCurso());
			eliminarAlumnos(c);
			stmt.execute();
			DataSource.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
	
	public boolean existeCurso(MateriaVO m, ProfesorVO p){
		boolean salida = false;
		String sql = "Select * from Curso Where nroMateria = ? and legProfesor = ?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, m.getNumero());
			stmt.setInt(2, p.getNroLegajo());
			rs = stmt.executeQuery();
			if(rs.next()){
				salida = true;			
			}
			DataSource.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return salida;
	}
	
		
	public boolean existeCursoConMateria(MateriaVO m ){
		boolean salida = false;
		String sql = "Select * from Curso Where nroMateria = ? ";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, m.getNumero());
			rs = stmt.executeQuery();
			if(rs.next()){
				salida = true;			
			}
			DataSource.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return salida;
	}	
		
	
	public boolean existeCursoConProfesor(ProfesorVO p ){
		boolean salida = false;
		String sql = "Select * from Curso Where legProfesor = ?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, p.getNroLegajo());
			rs = stmt.executeQuery();
			if(rs.next()){
				salida = true;			
			}
			DataSource.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return salida;
	}	
	
	
	
	
	//////////////////////////// Alumno_Curso //////////////////////////////////////////////
	
	
private void agregarAlumno(AlumnoVO a, CursoVO c){
		
		String sql = "Insert into Alumno_Curso values (?,?)";
		PreparedStatement stmt = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, a.getLegajo());
			stmt.setInt(2, c.getNumeroCurso());
			stmt.execute();
			DataSource.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
		
		
	private void guardarAlumnos(Vector<AlumnoVO> alumnos, CursoVO c){
	
		for(int i = 0; i<alumnos.size(); i++){
			AlumnoVO a = alumnos.elementAt(i);
			agregarAlumno(a,c);
	}
}




	private void actualizarAlumnos(Vector<AlumnoVO> alumnos, CursoVO c){
	
		eliminarAlumnos(c);
		for(int i = 0; i<alumnos.size(); i++){
			AlumnoVO a = alumnos.elementAt(i);
			agregarAlumno(a,c);
		}
	}


/*
	private void eliminarAlumno(AlumnoVO a, CursoVO c){
		
		String sql = "Delete Alumno_Curso Where legAlumno = ? and nroCurso = ?";
		PreparedStatement stmt = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, a.getLegajo());
			stmt.setInt(2, c.getNumeroCurso());
			stmt.execute();
			DataSource.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
	*/
	private Vector<AlumnoVO> obtenerAlumnos(CursoVO c){
		
	    Vector <AlumnoVO> alumnos = new Vector <AlumnoVO>();
		String sql = "Select * from Alumno_Curso Where nroCurso = ?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, c.getNumeroCurso());
			rs = stmt.executeQuery();
			while(rs.next()){
				AlumnoVO a = new AlumnoVO();
				a.setLegajo(rs.getInt(1));
		        a = AlumnoDAO.getInstancia().obtener(a);
		        alumnos.add(a);
			}
			DataSource.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return alumnos ;
		
	}
	
	
	private void eliminarAlumnos(CursoVO c){
		
		String sql = "Delete Alumno_Curso Where nroCurso = ?";
		PreparedStatement stmt = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, c.getNumeroCurso());
			stmt.execute();
			DataSource.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
	
	
	public boolean existeCursoConAlumno(AlumnoVO a ){
		boolean salida = false;
		String sql = "Select * from Alumno_Curso Where legAlumno = ?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			Connection con = DataSource.getConexion();
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, a.getLegajo());
			rs = stmt.executeQuery();
			if(rs.next()){
				salida = true;			
			}
			DataSource.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return salida;
	}	
	
}
